package com.wanmait.cookbook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.wanmait.cookbook.vo.Cookbook;
import com.wanmait.cookbook.vo.CookbookComment;
import com.wanmait.cookbook.vo.Userinfo;
import com.wanmait.cookbook.vo.Works;
import com.wanmait.cookbook.vo.WorksComment;
import com.wanmait.demo.util.DBHelper;

/*
 * 浣滃搧璇勮琛―AO
 */
public class WorksCommentDAO {
	private WorksCommentDAO() {}
	private static WorksCommentDAO worksCommentDAO = new WorksCommentDAO();
	public static WorksCommentDAO getWorksCommentDAO() {
		return worksCommentDAO;
	}
	
	  /**
	   *  鑾峰緱涓�绾ц瘎璁�
	   * @return
	   */
	  public ArrayList<WorksComment> getFirstComments(Integer worksId) {
	    ArrayList<WorksComment> allComments = new ArrayList<WorksComment>();
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    String sql = "SELECT * FROM works_comment,userinfo "
	    		+ "WHERE works_comment.works_id=? "
	    		+ "AND works_comment.userinfo_id=userinfo.id "
	    		+ "AND works_comment.parent_comment_id=0 "
	    		+ "and works_comment.enable=1 "
	    		+ "AND userinfo.mute=0";
	    try {
	      conn = DBHelper.getHelper().getConn();
	      ps = conn.prepareStatement(sql);
	      ps.setInt(1, worksId);
	      rs = ps.executeQuery();
	      WorksComment comment = null;
	      while (rs.next()) {
	        comment = new WorksComment();
	        comment.setId(rs.getInt("works_comment.id"));
	        Works works = new Works();
	        works.setId(rs.getInt("works_comment.works_id"));
	        comment.setWorks(works);
	        Userinfo userinfo = new Userinfo();
	        userinfo.setId(rs.getInt("userinfo.id"));
	        userinfo.setName(rs.getString("name"));
	        userinfo.setAvatar(rs.getString("avatar"));
	        comment.setUserinfo(userinfo);
	        Userinfo pU = new Userinfo();
	        pU.setId(0);
	        comment.setParentComment(pU);
	        comment.setContent(rs.getString("content"));
	        comment.setInputTime(rs.getTimestamp("works_comment.input_time"));
	        
	        this.getChidrenComments(comment, worksId);
	        allComments.add(comment);
	      }
	    } catch (SQLException e) {
	      // TODO Auto-generated catch block
	      e.printStackTrace();
	    }
	    finally {
	      DBHelper.getHelper().closeConn(rs, ps, conn);
	    }
	    return allComments;
	  }
	
	  /**
	   *  鑾峰緱璇勮鐨勬墍鏈夊瓙璇勮
	   * @param parent
	   * @param cookbookId
	   * @return
	   */
	  public ArrayList<WorksComment> getChidrenComments(WorksComment parent,Integer worksId) {
	    ArrayList<WorksComment> childrenComments = new ArrayList<WorksComment>();
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    String sql = "SELECT * FROM works_comment,userinfo "
	    		+ "WHERE works_comment.works_id=? "
	    		+ "AND works_comment.userinfo_id=userinfo.id "
	    		+ "AND works_comment.parent_comment_id=? "
	    		+ "and works_comment.enable=1  "
	    		+ "AND userinfo.mute=0";
	    try {
	      conn = DBHelper.getHelper().getConn();
	      ps = conn.prepareStatement(sql);
	      ps.setInt(1, worksId);
	      ps.setInt(2, parent.getId());
	      rs = ps.executeQuery();
	      WorksComment comment = null;
	      while (rs.next()) {
	        comment = new WorksComment();
	        comment.setId(rs.getInt("works_comment.id"));
	        Works works = new Works();
	        works.setId(worksId);
	        comment.setWorks(works);
	        Userinfo userinfo = new Userinfo();
	        userinfo.setId(rs.getInt("userinfo_id"));
	        userinfo.setName(rs.getString("name"));
	        userinfo.setAvatar(rs.getString("avatar"));
	        comment.setUserinfo(userinfo);
	        Userinfo uP = new Userinfo();
	        uP.setId(parent.getId());
	        comment.setParentComment(uP);
	        comment.setContent(rs.getString("content"));
	        comment.setInputTime(rs.getTimestamp("works_comment.input_time"));
	        
	        childrenComments.add(comment);
	        comment.setParent(parent);
	        
	        this.getChidrenComments(comment, worksId); // 閫掑綊鑾峰緱鎵�鏈夊瓙璇勮
	      }
	      parent.setChildren(childrenComments);
	    } catch (SQLException e) {
	      // TODO Auto-generated catch block
	      e.printStackTrace();
	    }
	    finally {
	      DBHelper.getHelper().closeConn(rs, ps, conn);
	    }
	    return childrenComments;
	  }
	  
	  /**
	   * 娣诲姞璇勮
	   * @param comment
	   */
	  public void addFirstComment(WorksComment comment) {
	    String sql = "INSERT INTO works_comment(works_id,userinfo_id,parent_comment_id,content) VALUES(?,?,?,?)";
	    DBHelper.getHelper().executeUpdate(sql, comment.getWorks().getId(),comment.getUserinfo().getId(),comment.getParentComment().getId(),comment.getContent());
	  }
	  
	
	//锟斤拷锟斤拷锟斤拷品锟斤拷锟桔碉拷id锟斤拷锟斤拷锟狡凤拷锟斤拷锟�
		public WorksComment getWorksCommentByCommentId(int id) {
			WorksComment worksComment = null;
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String sql="SELECT * FROM works_comment,userinfo "
					+ " WHERE works_comment.id=? "
					+ " AND works_comment.userinfo_id=userinfo.id";
			try {
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setInt(1, id);
				rs=ps.executeQuery();
				if(rs.next()) {
					worksComment= new WorksComment();
					worksComment.setId(rs.getInt("works_comment.id"));
					Userinfo userinfo = new Userinfo();
					userinfo.setId(rs.getInt("userinfo.id"));
					userinfo.setUsername(rs.getString("username"));
					worksComment.setUserinfo(userinfo);
					worksComment.setContent(rs.getString("content"));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return worksComment;
		}
		
		// 通锟斤拷ID删锟斤拷食锟斤拷锟斤拷锟斤拷
			public void deleteWorksCommentByid(int id) {
				String sql = "update works_comment set enable=0 where id=?";
				DBHelper.getHelper().executeUpdate(sql, id);
			}
			
			// 通锟斤拷IDs删锟斤拷锟斤拷锟绞筹拷锟斤拷锟斤拷锟�
			public void deleteWorksCommentsByIds(String ids) {
				String sql = "update works_comment set enable=0 where id in(" + ids + ")";
				DBHelper.getHelper().executeUpdate(sql);
			}
			//通过作品评论的id获得用户
			public Userinfo getUserinfoByCommentId(int id) {
				Userinfo userinfo=null;
				Connection conn = null;
				PreparedStatement ps = null;
				ResultSet rs = null;
				String sql="SELECT * FROM userinfo WHERE id=(SELECT userinfo_id FROM works_comment WHERE id=?)";
				try {
					conn=DBHelper.getHelper().getConn();
					ps=conn.prepareStatement(sql);
					ps.setInt(1, id);
					rs=ps.executeQuery();
					if(rs.next()) {
						userinfo = new Userinfo();
						userinfo.setId(rs.getInt("id"));
						userinfo.setName(rs.getString("name"));
						userinfo.setAvatar(rs.getString("avatar"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally {
					DBHelper.getHelper().closeConn(rs, ps, conn);
				}
				return userinfo;
			}
	
			//根据作品id查找用户
			public Userinfo getUserinfoByWorksId(int id) {
				Userinfo userinfo=null;
				Connection conn = null;
				PreparedStatement ps = null;
				ResultSet rs = null;
				String sql="SELECT * FROM userinfo WHERE id=(SELECT userinfo_id FROM works WHERE id=?)";
				try {
					conn=DBHelper.getHelper().getConn();
					ps=conn.prepareStatement(sql);
					ps.setInt(1, id);
					rs=ps.executeQuery();
					if(rs.next()) {
						userinfo = new Userinfo();
						userinfo.setId(rs.getInt("id"));
						userinfo.setName(rs.getString("name"));
						userinfo.setAvatar(rs.getString("avatar"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally {
					DBHelper.getHelper().closeConn(rs, ps, conn);
				}
				return userinfo;
			}
	
}
